--
-- Description: Script to save a file record.
-- Steps include new record in the file table if the file is new,
-- or update a record in the file table for file that has been updated.
-- Date created: 30 Apr 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the stored procedure if it exists and recreate it.
--
if exists
(
	select * 
	from sys.objects
	where object_id = object_id(N'[saveFile_sp]')
	and type in (N'P', N'PC')
)
begin
    drop procedure [dbo].[saveFile_sp]
end
go

create procedure [dbo].[saveFile_sp]
(
    @ProductID int
	, @FileID int
    , @FileName nvarchar(400)
	, @FileVersion nvarchar(400)
	, @DateCreated smalldatetime
	, @DateModified smalldatetime
)
as
begin
	--
	-- check if the file exists in the table.
	--
	if not exists
	(
		select top 1 1
		from dbo.Files
		where FileID = @FileID
	)
	begin
		--
		-- create a new record with the new FileID.
		--
		insert into [dbo].[Files]
		(
			[FileID]
			, [FileName]
			, [FileVersion]
			, [DateCreated]
			, [DateModified]
		)
		values
		(
			@FileID
			, @FileName
			, @FileVersion
			, @DateCreated
			, @DateModified
		)
	end
	else
	begin
		--
		-- Update the record for the DateModified column
		--
		update [dbo].[Files]
		set DateModified = @DateModified
		where @DateModified > DateModified
		and FileID = @FileID
	end

	--
	-- check if the ProductID/FileID record exists
	-- in the ProductsFiles table.
	--
	if not exists
	(
		select top 1 1
		from [dbo].[ProductsFiles]
		where ProductID = @ProductID
		and FileID = @FileID
	)
	begin
		--
		-- create a new record in the ProductsFiles table to
		-- establish the relationship between the product and the file.
		--
		insert into [dbo].[ProductsFiles]
		(
			[ProductID]
			, [FileID]
		)
		values
		(
			@ProductID
			, @FileID
		)
	end
end
go

set ansi_nulls off
go
set quoted_identifier off
go
